﻿/*****
 *  Copyright 2011, Kaven Gagné
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *  http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */

using System;
using System.Collections.Generic;
using Knowledge.DataModel;
using System.Data.SqlClient;
using System.IO;

namespace Knowledge
{
    /// <summary>
    /// Static Class used to manage database access and queries
    /// </summary>
    [Author("Kaven", Version = "1.0")]
    public static class Database
    {
        // Connection String
        private static String _datasource = "Data Source=kavenserver;Initial Catalog=KantechKnowledge;" +
                                            "Persist Security Info=True;User ID=kantech;Password=P@ssword";


        #region Articles Treeview
        
        #region GetFolders
        public static List<kFolder> GetFolders(kFolder parent)
        {
            List<kFolder> folders = new List<kFolder>();
            int id = (parent == null) ? 0 : parent.FolderID;
            string queryString = "SELECT id,title,description FROM tbFolders " +
                                 "WHERE (parentid = @Parent) AND (id <> 0) ORDER BY title";
            using (SqlConnection connection = new SqlConnection(_datasource))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@Parent", id);
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        folders.Add(new kFolder((int)reader["id"], (String)reader["title"], (String)reader["description"]));
                    }
                }
                catch (Exception e)
                {
                    Utilities.PrintError(e);
                }
            }
            return folders;
        }
        #endregion //GetFolders


        #region GetArticles
        public static List<kArticle> GetArticles(kFolder parent)
        {
            List<kArticle> articles = new List<kArticle>();
            int id = parent.FolderID;
            string queryString = "SELECT id,title,description FROM tbKnowledge " +
                                 "WHERE (parentid = @Parent) ORDER BY title";
            using (SqlConnection connection = new SqlConnection(_datasource))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@Parent", id);
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        articles.Add(new kArticle((int)reader["id"], (String)reader["title"], (String)reader["description"]));
                    }
                }
                catch (Exception e)
                {
                    Utilities.PrintError(e);
                }
            }
            return articles;
        }
        #endregion //GetArticles 


        #region AddArticle
        public static int AddArticle(string title, int parentid)
        {
            int rID;
            string queryString = "INSERT INTO tbKnowledge (parentid,title,description) VALUES (@Parent,@Title,'');" +
                                 "SELECT MAX(id) AS id FROM tbKnowledge WHERE (title = @Title)";
            using (SqlConnection connection = new SqlConnection(_datasource))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@Parent", parentid);
                command.Parameters.AddWithValue("@Title", title);
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        // Get ID of the Added Rec
                        rID = (int)reader["id"];
                    }
                    else
                    {
                        rID = (int)Utilities.errCode.NO_ROW_AFFECTED;
                    }
                }
                catch (Exception e)
                {
                    rID = (int)Utilities.errCode.EXCEPTION;
                    Utilities.PrintError(e);
                }
            }
            return rID;
        }
        #endregion //AddArticle


        #region AddFolder
        public static int AddFolder(string title, int parentid)
        {
            int rID;
            string queryString = "INSERT INTO tbFolders (parentid,title,description) VALUES (@Parent,@Title,'');" +
                                 "SELECT MAX(id) AS id FROM tbFolders WHERE (title = @Title)";
            using (SqlConnection connection = new SqlConnection(_datasource))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@Parent", parentid);
                command.Parameters.AddWithValue("@Title", title);
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        // Get ID of the Added Rec
                        rID = (int)reader["id"];
                    }
                    else
                    {
                        rID = (int)Utilities.errCode.NO_ROW_AFFECTED;
                    }
                }
                catch (Exception e)
                {
                    rID = (int)Utilities.errCode.EXCEPTION;
                    Utilities.PrintError(e);
                }
            }
            return rID;
        }
        #endregion //AddFolder

        #endregion //Articles Treeview




        #region Description

        #region GetDescription
        public static string GetDescription(Object parent, int id)
        {
            string desc = null;
            string queryString;
            if (parent is kArticle)
                queryString = "SELECT description FROM tbKnowledge WHERE (id = @ID)";
            else
                queryString = "SELECT description FROM tbFolders WHERE (id = @ID)";
            using (SqlConnection connection = new SqlConnection(_datasource))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@ID", id);
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        desc = (string)reader["description"];
                    }
                }
                catch (Exception e)
                {
                    Utilities.PrintError(e);
                }
            }
            return desc;
        }
        #endregion //GetDescription


        #region SetDescription
        public static int SetDescription(Object parent, int id, string desc)
        {
            int rID;
            string queryString;
            if (parent is kArticle)
                queryString = "UPDATE tbKnowledge SET description = @Desc WHERE (id = @ID)";
            else
                queryString = "UPDATE tbFolders SET description = @Desc WHERE (id = @ID)";
            using (SqlConnection connection = new SqlConnection(_datasource))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@Desc", desc);
                command.Parameters.AddWithValue("@ID", id);
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    rID = (reader.RecordsAffected == 0) ? (int)Utilities.errCode.NO_ROW_AFFECTED : reader.RecordsAffected;
                }
                catch (Exception e)
                {
                    rID = (int)Utilities.errCode.EXCEPTION;
                    Utilities.PrintError(e);
                }
            }
            return rID;
        }
        #endregion //SetDescription

        #endregion //Description




        #region LINKS
        
        #region GetLinks
        public static List<kLink> GetLinks(int id)
        {
            List<kLink> links = new List<kLink>();
            string queryString = "SELECT id,name,link FROM tbLinks " +
                                 "WHERE (parentid = @Parent) ORDER BY name";
            using (SqlConnection connection = new SqlConnection(_datasource))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@Parent", id);
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        links.Add(new kLink((int)reader["id"], (String)reader["name"], (String)reader["link"]));
                    }
                }
                catch (Exception e)
                {
                    Utilities.PrintError(e);
                }
            }
            return links;
        }
        #endregion //GetLinks


        #region AddLink
        public static int AddLink(string name, string link, int parentid)
        {
            int rID;
            string queryString = "INSERT INTO tbLinks (parentid,name,link) VALUES (@Parent,@Name,@Link);" +
                                 "SELECT MAX(id) AS id FROM tbLinks WHERE (name = @Name)";
            using (SqlConnection connection = new SqlConnection(_datasource))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@Parent", parentid);
                command.Parameters.AddWithValue("@Name", name);
                command.Parameters.AddWithValue("@Link", link);
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        // Get ID of the Added Rec
                        rID = (int)reader["id"];
                    }
                    else
                    {
                        rID = (int)Utilities.errCode.NO_ROW_AFFECTED;
                    }
                }
                catch (Exception e)
                {
                    if (e.Message.Contains("FOREIGN KEY"))
                        rID = (int)Utilities.errCode.FOREIGN_KEY_CONSTRAINT;
                    else
                    {
                        rID = (int)Utilities.errCode.EXCEPTION;
                        Utilities.PrintError(e);
                    }
                }
            }
            return rID;
        }
        #endregion //AddLink


        #region DeleteLink
        // Delete existing link in the database and returns ID of the link if successful, otherwise returns negative error code
        public static int DeleteLink(int linkid)
        {
            int rID;
            string queryString = "DELETE FROM tbLinks WHERE (id = @ID)";
            using (SqlConnection connection = new SqlConnection(_datasource))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@ID", linkid);
                try
                {
                    connection.Open();
                    int nRows = command.ExecuteNonQuery();
                    rID = (nRows > 0) ? linkid : (int)Utilities.errCode.NO_ROW_AFFECTED;
                }
                catch (Exception e)
                {
                    rID = (int)Utilities.errCode.EXCEPTION;
                    Utilities.PrintError(e);
                }
            }
            return rID;
        }
        #endregion //DeleteLink 

        #endregion //LINKS




        #region FILES

        #region GetFiles
        public static List<kFile> GetFiles(int id)
        {
            List<kFile> files = new List<kFile>();
            string queryString = "SELECT id,name FROM tbFiles " +
                                 "WHERE (parentid = @Parent) ORDER BY name";
            using (SqlConnection connection = new SqlConnection(_datasource))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@Parent", id);
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        files.Add(new kFile((int)reader["id"], (String)reader["name"]));
                    }
                }
                catch (Exception e)
                {
                    Utilities.PrintError(e);
                }
            }
            return files;
        }
        #endregion //GetFiles


        #region GetFileDataByID
        public static byte[] GetFileDataByID(int id)
        {
            byte[] file = null;
            string queryString = "SELECT id,filedata FROM tbFiles WHERE (id = @ID)";
            using (SqlConnection connection = new SqlConnection(_datasource))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@ID", id);
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        file = (byte[])reader["filedata"];
                    }
                }
                catch (Exception e)
                {
                    Utilities.PrintError(e);
                }
            }
            return file;
        }
        #endregion //GetFileDataByID


        #region AddFile
        // Add file to database and returns ID of this file if successful, otherwise returns negative error code
        public static int AddFile(FileInfo file, int parentid)
        {
            int rID;
            if (File.Exists(file.FullName))
            {
                string queryString = "INSERT INTO tbFiles (parentid,name,filedata) VALUES (@Parent,@Name,@Data);" +
                                     "SELECT MAX(id) AS id FROM tbFiles WHERE (name = @Name)";
                using (SqlConnection connection = new SqlConnection(_datasource))
                {
                    SqlCommand command = new SqlCommand(queryString, connection);
                    command.Parameters.AddWithValue("@Parent", parentid);
                    command.Parameters.AddWithValue("@Name", file.Name);
                    command.Parameters.AddWithValue("@Data", File.ReadAllBytes(file.FullName));
                    try
                    {
                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader();
                        if (reader.HasRows)
                        {
                            reader.Read();
                            // Get ID of the Added Rec
                            rID = (int)reader["id"];
                        }
                        else
                        {
                            rID = (int)Utilities.errCode.NO_ROW_AFFECTED;
                        }
                    }
                    catch (Exception e)
                    {
                        if (e.Message.Contains("FOREIGN KEY"))
                            rID = (int)Utilities.errCode.FOREIGN_KEY_CONSTRAINT;
                        else
                        {
                            rID = (int)Utilities.errCode.EXCEPTION;
                            Utilities.PrintError(e);
                        }
                    }
                }
            }
            else
            {
                rID = (int)Utilities.errCode.FILE_DONT_EXIST;
                Utilities.PrintError("AddFile: File does not exist: " + file.FullName);
            }
            return rID;
        }
        #endregion //AddFile


        #region ReplaceFile
        // Replace existing file in the database and returns ID of the file if successful, otherwise returns negative error code
        public static int ReplaceFile(FileInfo file, int fileid)
        {
            int rID;
            if (File.Exists(file.FullName))
            {
                string queryString = "UPDATE tbFiles SET filedata = @Data WHERE (id = @ID)";
                using (SqlConnection connection = new SqlConnection(_datasource))
                {
                    SqlCommand command = new SqlCommand(queryString, connection);
                    command.Parameters.AddWithValue("@Data", File.ReadAllBytes(file.FullName));
                    command.Parameters.AddWithValue("@ID", fileid);
                    try
                    {
                        connection.Open();
                        int nRows = command.ExecuteNonQuery();
                        rID = (nRows > 0) ? fileid : (int)Utilities.errCode.NO_ROW_AFFECTED;
                    }
                    catch (Exception e)
                    {
                        rID = (int)Utilities.errCode.EXCEPTION;
                        Utilities.PrintError(e);
                    }
                }
            }
            else
            {
                rID = (int)Utilities.errCode.FILE_DONT_EXIST;
                Utilities.PrintError("ReplaceFile: File does not exist: " + file.FullName);
            }
            return rID;
        }
        #endregion //ReplaceFile


        #region DeleteFile
        // Delete existing file in the database and returns ID of the file if successful, otherwise returns negative error code
        public static int DeleteFile(int fileid)
        {
            int rID;
            string queryString = "DELETE FROM tbFiles WHERE (id = @ID)";
            using (SqlConnection connection = new SqlConnection(_datasource))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@ID", fileid);
                try
                {
                    connection.Open();
                    int nRows = command.ExecuteNonQuery();
                    rID = (nRows > 0) ? fileid : (int)Utilities.errCode.NO_ROW_AFFECTED;
                }
                catch (Exception e)
                {
                    rID = (int)Utilities.errCode.EXCEPTION;
                    Utilities.PrintError(e);
                }
            }
            return rID;
        }
        #endregion //DeleteFile

        #endregion //FILES
    }
}
